CREATE TABLE PQViewSite(
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	SiteID int NOT NULL,
	StationKey varchar(20) NULL,
	LineKey varchar(20) NULL,
	PQIFacility int NULL,
	Enabled bit NOT NULL DEFAULT 1
)
GO

CREATE TABLE EDNAPoint(
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	LineID int NOT NULL FOREIGN KEY REFERENCES Line(ID),
	Point varchar(20) NOT NULL,
)
GO

ALTER TABLE EventData
ADD CycleData VARBINARY(MAX) NOT NULL
GO

CREATE NONCLUSTERED INDEX IX_Disturbance_PhaseID
ON Disturbance(PhaseID ASC)
GO

--DROP INDEX IX_BreakerRestrike_Timestamp
--GO

DROP TABLE BreakerRestrike
GO

CREATE TABLE BreakerRestrike (
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
    PhaseID INT NOT NULL REFERENCES Phase(ID),
	InitialExtinguishSample int NOT NULL,
	InitialExtinguishTime datetime2(7) NOT NULL,
	InitialExtinguishVoltage float NOT NULL,
	RestrikeSample int NOT NULL,
	RestrikeTime datetime2(7) NOT NULL,
	RestrikeVoltage float NOT NULL,
	RestrikeCurrentPeak float NOT NULL,
	RestrikeVoltageDip float NOT NULL,
	TransientPeakSample int NOT NULL,
	TransientPeakTime datetime2(7) NOT NULL,
	TransientPeakVoltage float NOT NULL,
	PerUnitTransientPeakVoltage float NOT NULL,
	FinalExtinguishSample int NOT NULL,
	FinalExtinguishTime datetime2(7) NOT NULL,
	FinalExtinguishVoltage float NOT NULL,
	I2t float NOT NULL,
)
GO

CREATE NONCLUSTERED INDEX IX_DisturbanceSeverity_VoltageEnvelopeID
ON DisturbanceSeverity(VoltageEnvelopeID ASC)
GO

ALTER TABLE FileGroupLocalToRemote
ADD RemoteXDAInstanceID int NOT NULL REFERENCES RemoteXDAInstance(ID)
GO

CREATE VIEW AssetGroupView AS
SELECT
	AssetGroup.ID,
	AssetGroup.Name,
	COUNT(DISTINCT AssetGroupAssetGroup.ChildAssetGroupID) as AssetGroups,
	COUNT(DISTINCT MeterAssetGroup.MeterID) as Meters,
	COUNT(DISTINCT LineAssetGroup.LineID) as Lines,
	COUNT(DISTINCT UserAccountAssetGroup.UserAccountID) as Users
FROM
	AssetGroup LEFT JOIN
	AssetGroupAssetGroup ON AssetGroup.ID = AssetGroupAssetGroup.ParentAssetGroupID LEFT JOIN
	MeterAssetGroup ON AssetGroup.ID = MeterAssetGroup.AssetGroupID LEFT JOIN
	LineAssetGroup ON AssetGroup.ID = LineAssetGroup.AssetGroupID LEFT JOIN
	UserAccountAssetGroup ON AssetGroup.ID = UserAccountAssetGroup.AssetGroupID
GROUP BY
	AssetGroup.ID,AssetGroup.Name
GO

ALTER PROCEDURE [dbo].[selectSiteLinesDetailsByDate]
    -- Add the parameters for the stored procedure here
    @EventDate as DateTime,
    @MeterID as nvarchar(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DATETIME = @EventDate
    DECLARE @endDate DATETIME

    IF @context = '180d'
    BEGIN
        SET @startDate = DATEADD(HOUR, -180, @EventDate)
        SET @endDate = @EventDate
    END

    IF @context = '90d'
    BEGIN
        SET @startDate = DATEADD(DAY, -90, @EventDate)
        SET @endDate = @EventDate
    END

    IF @context = '30d'
    BEGIN
        SET @startDate = DATEADD(DAY, -30, @EventDate)
        SET @endDate = @EventDate
    END

    IF @context = '7d'
    BEGIN
        SET @startDate = DATEADD(DAY, -7, @EventDate)
        SET @endDate = @EventDate
    END

    IF @context = '24h'
    BEGIN
        SET @startDate = DATEADD(HOUR, -24, @EventDate)
        SET @endDate = @EventDate
    END

    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END


    DECLARE @simStartDate DATETIME = DATEADD(SECOND, -5, @startDate)
    DECLARE @simEndDate DATETIME = DATEADD(SECOND, 5, @endDate)
    print @simStartDate
    print @simEndDate
    DECLARE @localEventDate DATE = CAST(@EventDate AS DATE)
    DECLARE @localMeterID INT = CAST(@MeterID AS INT)
    DECLARE @timeWindow int = (SELECT Value FROM DashSettings WHERE Name = 'System.TimeWindow')

	SELECT
		Event.ID,
		Event.LineID,
		EventType.Name AS EventType,
		Event.StartTime,
		MeterLine.LineName,
		Line.AssetKey AS LineKey,
		Line.VoltageKV AS LineVoltage,
		FaultSummary.FaultType,
		Disturbance.Type AS DisturbanceType,
		FaultSummary.Distance AS FaultDistance,
		Event.UpdatedBy
	INTO #event
    FROM
        Event JOIN
        EventType ON Event.EventTypeID = EventType.ID OUTER APPLY
		(
			SELECT TOP 1
				Disturbance.*,
				Phase.Name AS Type
			FROM
				Disturbance JOIN
				Phase ON Disturbance.PhaseID = Phase.ID
			WHERE
				EventID = Event.ID AND
				Phase.Name <> 'Worst'
			ORDER BY
				CASE EventType.Name
					WHEN 'Sag' THEN PerUnitMagnitude
					WHEN 'Swell' THEN -PerUnitMagnitude
					WHEN 'Interruption' THEN PerUnitMagnitude
					WHEN 'Transient' THEN -PerUnitMagnitude
				END,
				StartTime
		) Disturbance OUTER APPLY
		(
			SELECT TOP 1 *
			FROM FaultSummary
			WHERE EventID = Event.ID
			ORDER BY IsSelectedAlgorithm DESC, IsSuppressed, IsValid DESC, Inception
		) FaultSummary JOIN
        Meter ON Meter.ID = @MeterID JOIN
        Line ON Event.LineID = Line.ID JOIN
        MeterLine ON MeterLine.MeterID = @MeterID AND MeterLine.LineID = Line.ID
    WHERE
        Event.StartTime >= @startDate AND Event.StartTime < @endDate AND
        Event.MeterID = @localMeterID

	SELECT
        LineID AS thelineid,
        ID AS theeventid,
        EventType AS theeventtype,
        CAST(StartTime AS VARCHAR(26)) AS theinceptiontime,
        LineName + ' ' + LineKey AS thelinename,
        LineVoltage AS voltage,
        COALESCE(FaultType, DisturbanceType, '') AS thefaulttype,
        CASE WHEN FaultDistance = '-1E308' THEN 'NaN' ELSE COALESCE(CAST(CAST(FaultDistance AS DECIMAL(16, 4)) AS NVARCHAR(19)), '') END AS thecurrentdistance,
        dbo.EventHasImpactedComponents(ID) AS pqiexists,
        StartTime,
        (SELECT COUNT(*) FROM Event as EventCount WHERE EventCount.StartTime BETWEEN DateAdd(SECOND, -5, Event.StartTime) and  DateAdd(SECOND, 5, Event.StartTime)) as SimultaneousCount,
        (SELECT COUNT(*) FROM Event as EventCount WHERE EventTypeID IN (SELECT ID FROM EventType WHERE Name = 'Sag' OR Name = 'Fault') AND EventCount.StartTime BETWEEN DateAdd(SECOND, -@timeWindow, Event.StartTime) and  DateAdd(SECOND, @timeWindow, Event.StartTime)) as SimultaneousFAndSCount,
        (SELECT COUNT(*) FROM Event as EventCount WHERE EventCount.LineID = Event.LineID AND EventCount.StartTime BETWEEN DateAdd(Day, -60, Event.StartTime) and  Event.StartTime) as SixtyDayCount,
        UpdatedBy,
        (SELECT COUNT(*) FROM EventNote WHERE EventID = Event.ID) as Note
	INTO #temp
	FROM #event Event

    DECLARE @sql NVARCHAR(MAX)
    SELECT @sql = COALESCE(@sql + ',dbo.' + HasResultFunction + '(theeventid) AS ' + ServiceName, 'dbo.' + HasResultFunction + '(theeventid) AS ' + ServiceName)
    FROM EASExtension

    DECLARE @serviceList NVARCHAR(MAX)
    SELECT @serviceList = COALESCE(@serviceList + ',' + ServiceName, ServiceName)
    FROM EASExtension
    Set @serviceList = '''' + @serviceList + ''''


    SET @sql = COALESCE('SELECT *,' + @sql + ', '+ @ServiceList +'as ServiceList FROM #temp', 'SELECT *, '''' AS ServiceList FROM #temp')
    print @sql
    EXEC sp_executesql @sql

    DROP TABLE #temp
	DROP TABLE #event

END
GO

ALTER PROCEDURE [dbo].[selectSitesBreakersDetailsByDate]
    @EventDate AS DATETIME,
    @MeterID AS NVARCHAR(MAX),
    @username AS NVARCHAR(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime
    DECLARE @endDate DateTime

    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END

	SELECT * INTO #MeterSelection FROM dbo.String_to_int_table(@MeterID, ',')
    SELECT
        Meter.ID AS meterid,
        Event.ID AS theeventid,
        EventType.Name AS eventtype,
        BreakerOperation.ID AS breakeroperationid,
        CAST(CAST(BreakerOperation.TripCoilEnergized AS TIME) AS NVARCHAR(100)) AS energized,
        BreakerOperation.BreakerNumber AS breakernumber,
        MeterLine.LineName AS linename,
        Phase.Name AS phasename,
        CAST(BreakerOperation.BreakerTiming AS DECIMAL(16,5)) AS timing,
        CAST(BreakerOperation.StatusTiming AS DECIMAL(16,5)) AS statustiming,
        BreakerOperation.BreakerSpeed AS speed,
        BreakerOperation.StatusBitChatter AS chatter,
        BreakerOperation.DcOffsetDetected AS dcoffset,
        BreakerOperationType.Name AS operationtype,
        (SELECT COUNT(*) FROM EventNote WHERE EventNote.EventID = Event.ID) as notecount
    FROM
        BreakerOperation JOIN
        Event ON BreakerOperation.EventID = Event.ID JOIN
        EventType ON EventType.ID = Event.EventTypeID JOIN
        Meter ON Meter.ID = Event.MeterID JOIN
        Line ON Line.ID = Event.LineID JOIN
        MeterLine ON MeterLine.LineID = Event.LineID AND MeterLine.MeterID = Meter.ID JOIN
        BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN
        Phase ON BreakerOperation.PhaseID = Phase.ID
    WHERE
        TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate AND
        Meter.ID IN (SELECT * FROM  #MeterSelection)
END
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('MaxEventDuration', '0.0', '0.0')
GO